Projects

Automation of Large Insurance Data Processing

Automation
Database Management
Data Processing

July 2022

Access logo on red background

Description

This project aimed to automate the processing of large-scale banking data by extracting information from massive CSV files and classifying them into more accessible Excel files based on predefined criteria. The tool enhanced data accessibility and streamlined storage.

I developed a VBA (Visual Basic for Applications) script on Access, featuring:

  • A user-friendly interface and flexibility to handle changes in source file structures.
  • Automated browsing of CSV files, integrating data into an Access database.
  • Execution of advanced queries on AS/400 systems using an integrated ODBC driver within the VBA code.
  • Training on AS/400 systems to write optimized queries for data aggregation and extraction.
  • Automated generation of Excel summary tables for statistical analysis.
  • Execution of SQL queries to extract and export results to Excel files.

The tool achieved an estimated time saving of 20 man-days compared to manual processes, while significantly improving the reliability of regulatory data processing by eliminating manual errors in file handling and summary creation.

Technology used

  • Access
  • Excel
  • VBA
  • AS/400
  • SQL
  • ODBC Driver